const router = require('koa-router')()

// 生成雪花ID的方法
const createSnow = function (ctx) {
    let snowFlake = ctx.snowFlake;
    let snowId = snowFlake.createSnow(8);
    return snowId;
}

router.prefix('/personal')

// 修改用户信息
router.post('/changeinfo', async (ctx, next) => {
    let { user_name, signature, teacher_experience, user_id, user_auth, option } = ctx.request.body
    // 修改昵称
    if (option == 'user_name' && user_auth == 'teacher_per') {
        let msgObj = {}
        let hasSql = 'select user_name from user where user_name=? and user_id != ?'
        let hasRes = await ctx.db.EXCUTE(hasSql, [user_name, user_id])
        console.log(hasRes);
        if (hasRes.length == 0) {
            let sql = 'update user set user_name=? where user_id=?'
            let res = await ctx.db.EXCUTE(sql, [user_name, user_id])
            console.log(res);
            if (res.affectedRows != 0) {//user表修改成功
                let sql = 'update teacher set user_name=? where user_id=?'
                let res = await ctx.db.EXCUTE(sql, [user_name, user_id])
                if (res.affectedRows != 0) {
                    msgObj = { code: 200, msg: '昵称修改成功' }
                }
            }
        } else {
            msgObj = { code: 201, msg: '该用户名已存在，请换一个试试' }
        }
        ctx.body = msgObj
    }
    if (option == 'user_name' && user_auth == 'student_per') {
        let msgObj = {}
        let hasSql = 'select user_name from user where user_name=? and user_id != ?'
        let hasRes = await ctx.db.EXCUTE(hasSql, [user_name, user_id])
        console.log(hasRes);
        if (hasRes.length == 0) {
            let sql = 'update user set user_name=? where user_id=?'
            let res = await ctx.db.EXCUTE(sql, [user_name, user_id])
            console.log(res);
            if (res.affectedRows != 0) {//user表修改成功
                let sql = 'update student set user_name=? where user_id=?'
                let res = await ctx.db.EXCUTE(sql, [user_name, user_id])
                if (res.affectedRows != 0) {
                    msgObj = { code: 200, msg: '昵称修改成功' }
                }
            }
        } else {
            msgObj = { code: 201, msg: '该用户名已存在，请换一个试试' }
        }
        ctx.body = msgObj
    }
    // 修改老师个签
    if (option == 'signature' && user_auth == 'teacher_per') {
        let sql = 'update teacher set signature=? where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [signature, user_id])
        console.log(res);
        if (res.affectedRows != 0) {//teacher表修改成功
            ctx.body = { code: 200, msg: '个签修改成功' }
        }
    }
    // 修改学生个签
    if (option == 'signature' && user_auth == 'student_per') {
        let sql = 'update student set signature=? where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [signature, user_id])
        console.log(res);
        if (res.affectedRows != 0) {//student表修改成功
            ctx.body = { code: 200, msg: '个签修改成功' }
        }
    }
    // 修改教师经历
    if (option == 'teacher_experience') {
        let sql = 'update teacher set teacher_experience=? where user_id=?'
        let res = await ctx.db.EXCUTE(sql, [teacher_experience, user_id])
        console.log(res);
        if (res.affectedRows != 0) {//teacher表修改成功
            ctx.body = { code: 200, msg: '经历修改成功' }
        }
    }
})
// 通过用户的身份查找用户的课程
router.post('/gitcoursebyid', async (ctx, next) => {
    let { currentPage, id, auth } = ctx.request.body

    if (auth == 'teacher_per') {
        let sql = 'select * from course where teacher_id=?'
        let res = await ctx.db.EXCUTE(sql, [id])
        console.log(res);
        ctx.body = { code: 200, msg: "成功查询", list: res.slice((currentPage - 1) * 9, currentPage * 9), total: res.length }
    }
    if (auth == 'student_per') {
        // 联表查询
        let lbsql = 'select * from course inner join student_course_relation on course.course_id=student_course_relation.course_id where student_course_relation.student_id=?'
        let lbres = await ctx.db.EXCUTE(lbsql, [id])
        ctx.body = { code: 200, msg: "成功查询", list: lbres.slice((currentPage - 1) * 9, currentPage * 9), total: lbres.length }
    }

})
// 获取学生或老师的任务
router.post('/getassignment', async (ctx, next) => {
    let { id, auth, currentPage } = ctx.request.body
    if (auth == 'student_per') {
        let sql = 'select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,course.course_id,course.course_name,teacher.teacher_id,teacher.teacher_name,student_submit_assignment.submit_status,student_submit_assignment.correction_status, course.course_id from assignment join (select course_id from student_course_relation where student_id=?)subquery on assignment.course_id=subquery.course_id join course on course.course_id=assignment.course_id join teacher on teacher.teacher_id=assignment.teacher_id join student_submit_assignment on student_submit_assignment.assignment_id=assignment.assignment_id and student_submit_assignment.student_id=?'
        let res = await ctx.db.EXCUTE(sql, [id, id])
        console.log(res, 'assignment');
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
    if (auth == 'teacher_per') {
        let sql = 'select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,assignment.publish_number,assignment.assignment_status,course.course_id,course.course_name from assignment join course on course.course_id=assignment.course_id where assignment.teacher_id=?'
        let res = await ctx.db.EXCUTE(sql, [id])
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
})
// 搜索学生或老师的任务
router.post('/findassignment', async (ctx, next) => {
    let { id, auth, currentPage, keyword } = ctx.request.body
    if (auth == 'student_per') {
        let sql = `select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,course.course_id,course.course_name,teacher.teacher_id,teacher.teacher_name,student_submit_assignment.submit_status,student_submit_assignment.correction_status from assignment join (select course_id from student_course_relation where student_id=?)subquery on assignment.course_id=subquery.course_id join course on course.course_id=assignment.course_id join teacher on teacher.teacher_id=assignment.teacher_id join student_submit_assignment on student_submit_assignment.assignment_id=assignment.assignment_id and student_submit_assignment.student_id=? where assignment.assignment_title LIKE '%${keyword}%'`
        let res = await ctx.db.EXCUTE(sql, [id, id])
        console.log(res, 'assignment');
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
    if (auth == 'teacher_per') {
        let sql = `select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,assignment.publish_number,assignment.assignment_status,course.course_id,course.course_name from assignment join course on course.course_id=assignment.course_id where assignment.teacher_id=? and assignment.assignment_title LIKE '%${keyword}%'`
        let res = await ctx.db.EXCUTE(sql, [id])
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
})
// 搜索某课程的任务
router.post('/findcassignment', async (ctx, next) => {
    let { id, auth, currentPage, keyword, course_id } = ctx.request.body
    if (auth == 'student_per') {
        let sql = `select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,course.course_id,course.course_name,teacher.teacher_id,teacher.teacher_name,student_submit_assignment.submit_status,student_submit_assignment.correction_status from assignment join course on course.course_id=assignment.course_id join teacher on teacher.teacher_id=assignment.teacher_id join student_submit_assignment on student_submit_assignment.assignment_id=assignment.assignment_id and student_submit_assignment.student_id=? where assignment.course_id=? and assignment.assignment_title LIKE '%${keyword}%'`
        let res = await ctx.db.EXCUTE(sql, [id, course_id])
        console.log(res, 'assignment');
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
    if (auth == 'teacher_per') {
        let sql = `select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,assignment.publish_number,assignment.assignment_status,course.course_id,course.course_name from assignment join course on course.course_id=assignment.course_id where assignment.teacher_id=? and assignment.course_id=? and assignment.assignment_title LIKE '%${keyword}%'`
        let res = await ctx.db.EXCUTE(sql, [id, course_id])
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
})
// 获取某课程的任务
router.post('/getassbycid', async (ctx, next) => {
    let { id, auth, currentPage, course_id } = ctx.request.body
    console.log(course_id);
    if (auth == 'student_per') {
        let sql = `select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,course.course_id,course.course_name,teacher.teacher_id,teacher.teacher_name,student_submit_assignment.submit_status,student_submit_assignment.correction_status from assignment join course on course.course_id=assignment.course_id join teacher on teacher.teacher_id=assignment.teacher_id join student_submit_assignment on student_submit_assignment.assignment_id=assignment.assignment_id and student_submit_assignment.student_id=? where assignment.course_id=?`
        let res = await ctx.db.EXCUTE(sql, [id, course_id])
        console.log(res, 'assignment');
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
    if (auth == 'teacher_per') {
        let sql = `select assignment.assignment_id,assignment.assignment_title,assignment.publish_time,assignment.deadline,assignment.total_points,assignment.publish_number,assignment.assignment_status,course.course_id,course.course_name from assignment join course on course.course_id=assignment.course_id where assignment.teacher_id=? and assignment.course_id=?`
        let res = await ctx.db.EXCUTE(sql, [id, course_id])
        let list = res.map(item => {
            return { ...item, publish_time: ctx.formTime.formatDate(item.publish_time), deadline: ctx.formTime.formatDate(item.deadline) }
        })
        ctx.body = { code: 200, msg: '查询成功', list: list.slice((currentPage - 1) * 4, currentPage * 4), total: res.length }
    }
})

module.exports = router